/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAL;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.HashMap;

/**
 *
 * @author MINHTU
 */
public class MyConnectUnit {
    
  
    //Bien ket noi co ban
    private MySQLConnect connectMySQL;
    
       // Hàm khởi tạo và truyền thông tin của DataBase Server.
    public MyConnectUnit(){}
    
    //ham ho tro select CSDL
    //Select * from TableName where Condition Order By OrderBy 
    public ResultSet Select(String TableName, String Condition, String OrderBy) throws Exception{
        //Khai bao bien StringBuilder de tao chuoi Select
        StringBuilder query = new StringBuilder("Select * from " + TableName);
        // Đưa câu lệnh điều kiện vào trong câu query
        this.AddCondition(query, Condition);
        //Đưa câu lệnh orderby vào trong câu query
        this.AddOrderBy(query, OrderBy);
        //chèn ký tự ";" vào cuối dòng lệnh 
        query.append(";");
        //Thực thi câu query và trả kết quả ra ngoài
        return this.connectMySQL.excuteQuery(query.toString());
    }
    
    //Hàm Over load Select Giam OrderBy parameter
    public ResultSet Select(String TableName, String Condition) throws Exception{
        return this.Select(TableName, Condition, null);
    }
    
     //Hàm Over load Select Giam Condition parameter
    public ResultSet Select(String TableName) throws Exception{
        return this.Select(TableName, null);
    }
    
    
    //Ham Insert
    //Insert Into TableName (columnname...) values (column values....)
    public boolean Insert(String TableName, HashMap<String,Object> ColumnValues) throws Exception{
        StringBuilder query = new StringBuilder("INSERT INTO " + TableName);
        StringBuilder valueInsert = new StringBuilder();
        
        query.append("(");
        //Duyet va dua thong tin ten cot va gia tri cua cot vao
        for(String key : ColumnValues.keySet()){
            query.append(key).append(",");
            valueInsert.append("'").append(ColumnValues.get(key).toString()).append("',");  
        }
        
        // cat bot ky tu "," cuoi cau
        query = query.delete(query.length() -1, query.length());
        valueInsert = valueInsert.delete(valueInsert.length() -1, valueInsert.length());
        
        //dua gia tri cua cot vao cau query
        query.append(") VALUES (").append(valueInsert.toString()).append(")");
        query.append(";");
        
        //Thuc thi cau query va tra ket qua
        return this.connectMySQL.executeUpdate(query.toString()) > 0;  
    }
    
    //update
    public boolean Update(String TableName, HashMap<String,Object> ColumnValues, String Condition) throws Exception{
        StringBuilder query = new StringBuilder("UPDATE " + TableName + " SET ");
       
        
        //Duyet va dua thong tin ten cot va gia tri cua cot vao
        for(String key : ColumnValues.keySet()){
            query.append(key).append(" = '").append(ColumnValues.get(key).toString()).append("' ,");  
        }
        
        // cat bot ky tu "," cuoi cau
        query = query.delete(query.length() -1, query.length());
        
       this.AddCondition(query, Condition);
        query.append(";");
        
        //Thuc thi cau query va tra ket qua
        return this.connectMySQL.executeUpdate(query.toString()) > 0;  
    }
    
    public boolean Delete(String TableName, String Condition) throws Exception{
        StringBuilder query = new StringBuilder("DELETE FROM " + TableName);
       
        this.AddCondition(query, Condition);
        query.append(";");
        
        //Thuc thi cau query va tra ket qua
        return this.connectMySQL.executeUpdate(query.toString()) > 0;  
    }
    
    
    public static int getColumnCount(ResultSet result) throws SQLException{
        return result.getMetaData().getColumnCount();
        
    }
    
    public static String[] getColumnName(ResultSet result) throws SQLException{
        ResultSetMetaData rsMetaData = (ResultSetMetaData)result.getMetaData();
        int ColumnCount = rsMetaData.getColumnCount();
        //khai bao danh sach cac cot
        String[] list = new String[ColumnCount];
        
        //duyet cac cot
        for(int i=0; i<ColumnCount;i++){
            //lay ten dua vao danh sach
            list[i] =rsMetaData.getColumnName(i);
        }
        
        //tra danh sach ra ngoai
        return list;
    }
    
    
    private void AddCondition(StringBuilder query, String Condition){
        //Kiem tra neu condition khac null
        if(Condition != null)
            query.append((" WHERE " + Condition));
    }
    
    private void AddOrderBy(StringBuilder query, String OrderBy){
        //Kiem tra neu condition khac null
        if(OrderBy != null)
            query.append((" ORDER BY " + OrderBy));
    }
    
    // Hàm khởi tạo và truyền thông tin của DataBase Server.
    public MyConnectUnit(String Host,
            String UserName,
            String Password,
            String DataBase){
        this.connectMySQL = new MySQLConnect(Host, UserName, Password, DataBase);
    }

     public void Close() throws SQLException {
        this.connectMySQL.Close();
    }

}
